package cn.itcast.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.itcast.utils.MyJdbcUtil;
import cn.itcast.vo.Customer;
import cn.itcast.vo.PageBean;
import lombok.Cleanup;

public class PreparedStatementCustomerDaoImpl implements CustomerDao {

	@Override
	public int save(Customer c) {
	    String sql = "insert into t_customer values (?,?,?,?,?,?,?,?)";

	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup PreparedStatement ps = conn.prepareStatement(sql);
	    	
	    	ps.setString(1, c.getId());
	    	ps.setString(2, c.getUsername());
	    	ps.setString(3, c.getGender());
	    	ps.setString(4, c.getBirthday());
	    	ps.setString(5, c.getCellphone());
	    	ps.setString(6, c.getEmail());
	    	ps.setString(7, c.getLove());
	    	ps.setString(8, c.getType());
	    	
	    	return ps.executeUpdate();
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public List<Customer> findAll() {
		String sql = "select * from t_customer";
	    List<Customer> list = new ArrayList<Customer>();
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup PreparedStatement ps = conn.prepareStatement(sql);
	    	@Cleanup ResultSet rs = ps.executeQuery();
	    	
	    	while (rs.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	list.add(c);
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return list;
	}

	@Override
	public int delete(String id) {
		String sql = "delete from t_customer where id = ?";

	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup PreparedStatement ps = conn.prepareStatement(sql);
	    	ps.setString(1, id);
	    	
	    	return ps.executeUpdate();
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public Customer findById(String id) {
		String sql = "select * from t_customer where id = ?";
	    Customer c = null;
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup PreparedStatement ps = conn.prepareStatement(sql);
	    	ps.setString(1, id);
	    	
	    	@Cleanup ResultSet rs = ps.executeQuery();
	    	
	    	while (rs.next()) {
	    		c = new Customer();
	    		
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	break;
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }

	    return c;
	}

	@Override
	public int update(Customer c) {
	    String sql = "update t_customer set username = ?,gender=?,birthday=?,cellphone=?,email=?,love=?,type=? where id = ?";
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup PreparedStatement ps = conn.prepareStatement(sql);
	    	ps.setString(1, c.getUsername());
	    	ps.setString(2, c.getGender());
	    	ps.setString(3, c.getBirthday());
	    	ps.setString(4, c.getCellphone());
	    	ps.setString(5, c.getEmail());
	    	ps.setString(6, c.getLove());
	    	ps.setString(7, c.getType());
	    	ps.setString(8, c.getId());
	    	
	    	//ps.addBatch();
	    	
	    	//int [] ret =  ps.executeBatch();
	    	
	    	// return ret[0];
	    	
	    	return ps.executeUpdate();
	    	
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	}

	@Override
	public List<Customer> findByWhere(String username, String type) {
		StringBuffer sb = new StringBuffer("select * from t_customer where 1=1 ");
		List<String> paraList = new ArrayList<String>();
		
		if ((username != null) && (!username.trim().isEmpty()))
	    {
	      sb.append(" and username = ? ");
	      paraList.add(username);
	    }
	    if ((type != null) && (!type.trim().isEmpty()))
	    {
	      sb.append(" and type=? ");
	      paraList.add(type);
	    }

	    List<Customer> list = new ArrayList<Customer>();
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	@Cleanup PreparedStatement ps = conn.prepareStatement(sb.toString());
	    	
	    	for (int i = 0; i < paraList.size(); ++i) {
	    		ps.setString(i + 1, paraList.get(i));
	    	}
	    	
	    	@Cleanup ResultSet rs = ps.executeQuery();
	    	
	    	while (rs.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs.getString(1));
		    	c.setUsername(rs.getString(2));
		    	c.setGender(rs.getString(3));
		    	c.setBirthday(rs.getString(4));
		    	c.setCellphone(rs.getString(5));
		    	c.setEmail(rs.getString(6));
		    	c.setLove(rs.getString(7));
		    	c.setType(rs.getString(8));
		    	
		    	list.add(c);
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return list;
	}

	@Override
	public PageBean<Customer> findByPage(int pageCode, int pageSize) {
		PageBean<Customer> page = new PageBean<Customer>();
	    page.setPageCode(pageCode);
	    page.setPageSize(pageSize);

	    List<Customer> beanList = new ArrayList<Customer>();
	    
	    String countSql = "select count(*) from t_customer";
	    
	    try {
	    	@Cleanup Connection conn = MyJdbcUtil.getConnection();
	    	int count = -1;
	    	@Cleanup PreparedStatement ps1 = conn.prepareStatement(countSql);
	    	if (ps1.execute()) {
	    		@Cleanup ResultSet rs1 = ps1.getResultSet();
	    		
		    	while (rs1.next()) {
		    		count = (int) rs1.getLong(1);
		    		break;
		    	}
		    	
		    	page.setTotalCount(count);
	    	}

	    	String selSql = "";
	    	int m, n;
			
			if (MyJdbcUtil.isSqlServer) {
				selSql = "select top %d * from t_customer where id not in (select top %d id from t_customer)";
				m = pageSize;
				n = Integer.valueOf((pageCode - 1) * pageSize);
			} else if (MyJdbcUtil.isOracleServer) {
				selSql = "SELECT * FROM " +
				"(" +
				"SELECT A.*, ROWNUM RN " +  
				"FROM (SELECT * FROM t_customer) A " +
				"WHERE ROWNUM <= %d" +
				")" +
				"WHERE RN > %d";
				
				n = Integer.valueOf((pageCode - 1) * pageSize);
				m = n + pageSize;
			} else if (MyJdbcUtil.isPostgresServer) {
				selSql = "select * from t_customer limit %d offset %d";
				n = Integer.valueOf((pageCode - 1) * pageSize);
				m = Integer.valueOf(pageSize);
			} else if (MyJdbcUtil.isInformixServer) {
				selSql = "SELECT SKIP %d FIRST %d * FROM t_customer";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(pageSize);
			} else if (MyJdbcUtil.isSybaseServer) {
				// 创建临时表
				@Cleanup PreparedStatement createTmpPs = conn.prepareStatement("SELECT convert (varchar,id) as id, convert (varchar,username) as username, convert (varchar,gender) as gender, convert (varchar,birthday) as birthday, convert (varchar,cellphone) as cellphone, convert (varchar,email) as email, convert (varchar,love) as love, convert (varchar,type) as type,sybid=identity(12) into #temp FROM t_customer");
				createTmpPs.execute();
				
				selSql = "select * from #temp where sybid > %d and sybid <= %d";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(m + pageSize);
			} else {
				selSql = "select * from t_customer limit %d,%d";
				m = Integer.valueOf((pageCode - 1) * pageSize);
				n = Integer.valueOf(pageSize);
			}
			
			@Cleanup PreparedStatement ps2 = conn.prepareStatement(String.format(selSql, m, n));
	    	@Cleanup ResultSet rs2 = ps2.executeQuery();
	    	
	    	while (rs2.next()) {
	    		Customer c = new Customer();
		    	
		    	c.setId(rs2.getString(1));
		    	c.setUsername(rs2.getString(2));
		    	c.setGender(rs2.getString(3));
		    	c.setBirthday(rs2.getString(4));
		    	c.setCellphone(rs2.getString(5));
		    	c.setEmail(rs2.getString(6));
		    	c.setLove(rs2.getString(7));
		    	c.setType(rs2.getString(8));
		    	
		    	beanList.add(c);
	    	}
	    	
	    	page.setBeanList(beanList);
	    	
	    	if (MyJdbcUtil.isSybaseServer) {
				// 删除临时表
				@Cleanup PreparedStatement createTmpPs = conn.prepareStatement("drop table #temp");
				createTmpPs.execute();
	    	}
	    }
	    catch (SQLException e) {
	      e.printStackTrace();
	      throw new RuntimeException(e);
	    }
	    
	    return page;
	    
	}

}
